package com.firexxx.study.util;

import com.alibaba.fastjson.JSON;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtil {
    private final static String APPID = "appid";
    private final static String CHANNEL_ID = "channelId";
    private final static String SENSITIVE_WORLD = "sensitiveWord";

    // 读取单元格内容
    public static String getValue(XSSFCell hssfCell) {
        if (hssfCell == null){
            return null;
        }
        if (hssfCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
                Date date = hssfCell.getDateCellValue();
                return sdf.format(date);
            } else if (hssfCell.getCellStyle().getDataFormat() == 31) {
                // 自定义日期格式：处理yyyy年m月d日,h时mm分,yyyy年m月等含文字的日期格式
                // 处理自定义日期格式：yyyy年m月d日(通过判断单元格的格式id解决，id的值是31)
                double value = hssfCell.getNumericCellValue();
                Date date = org.apache.poi.ss.usermodel.DateUtil
                        .getJavaDate(value);
                return sdf.format(date);
            }
            // 返回数值类型的值
            DecimalFormat df = new DecimalFormat("0");
            String whatYourWant = df.format(hssfCell.getNumericCellValue());
            return whatYourWant;//String.valueOf(hssfCell.getNumericCellValue());
        } else {
            // 返回字符串类型的值
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    // 读取单元格内容 Excel为xls
    public static String getValue(HSSFCell hssfCell) {
        if (hssfCell == null)
            return null;
        if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
                Date date = hssfCell.getDateCellValue();
                return sdf.format(date);
            } else if (hssfCell.getCellStyle().getDataFormat() == 31) {
                // 自定义日期格式：处理yyyy年m月d日,h时mm分,yyyy年m月等含文字的日期格式
                // 处理自定义日期格式：yyyy年m月d日(通过判断单元格的格式id解决，id的值是31)
                double value = hssfCell.getNumericCellValue();
                Date date = org.apache.poi.ss.usermodel.DateUtil
                        .getJavaDate(value);
                return sdf.format(date);
            }
            // 返回数值类型的值
            DecimalFormat df = new DecimalFormat("0");
            String whatYourWant = df.format(hssfCell.getNumericCellValue());
            return whatYourWant;
        } else {
            // 返回字符串类型的值
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    /**
     * @Author yztao2@iflytek.com
     * @Description 读取单元格内容
     * @Date 10:58 2020/11/3
     * @Param [hssfCell]
     * @return java.lang.Integer
     **/
    public static Integer getIntValue(XSSFCell hssfCell) {
        String value = getValue(hssfCell);
        if (value == null){
            return null;
        }else {
            return Integer.valueOf(value);
        }
    }

    public static CellRangeAddress getMergedRegion(XSSFSheet sheet, Cell cell) {
        return getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());
    }

    public static CellRangeAddress getMergedRegion(XSSFSheet sheet, int rowIndex, int colIndex) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (rowIndex >= firstRow && rowIndex <= lastRow) {
                if (colIndex >= firstColumn && colIndex <= lastColumn) {
                    CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn);
                    region.setFirstRow(firstRow);
                    region.setLastRow(lastRow);
                    region.setFirstColumn(firstColumn);
                    region.setLastColumn(lastColumn);
                    return region;
                }
            }
        }
        return null;
    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param hssfRow
     * @param cell
     * @return
     */
    public static String getMergedRegionValue(XSSFSheet sheet, XSSFRow hssfRow, XSSFCell cell) {
        if (cell == null){
            return null;
        }
        int sheetMergeCount = sheet.getNumMergedRegions();
        int row = hssfRow.getRowNum();
        int column = cell.getColumnIndex();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    XSSFRow fRow = sheet.getRow(firstRow);
                    XSSFCell fCell = fRow.getCell(firstColumn);
                    return ExcelUtil.getValue(fCell);
                }
            }
        }

        return ExcelUtil.getValue(cell);
    }

    public static Map<String, Map<String, String>> readAndParse(String path) {
        InputStream inStream = null;
        try {
            inStream = new FileInputStream(new File(path));
            return readXlsx(inStream);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 读取后缀为xlsx的excel
     *
     * @param is
     * @return
     * @throws IOException
     */
    public static Map<String, Map<String, String>> readXlsx(InputStream is) throws IOException {
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
        Map<String, Map<String, String>> map = new HashMap<>();

        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    String appid = getStringValue(hssfRow.getCell(0)).trim();
                    String channelId = getStringValue(hssfRow.getCell(1)).trim();
                    String sensitiveWorld = getStringValue(hssfRow.getCell(2)).trim();
                    Map<String, String> rowMap = new HashMap<>();
                    if (StringUtils.isNotEmpty(channelId) && StringUtils.isNotEmpty(sensitiveWorld)) {
                        rowMap.put(APPID, appid);
                        rowMap.put(CHANNEL_ID, channelId);
                        rowMap.put(SENSITIVE_WORLD, sensitiveWorld);
                        map.put(String.valueOf(numSheet) + "_" + String.valueOf(rowNum), rowMap);
                    }

                }
            }
        }
        return map;
    }


    public static Map<String, List<String>> readXls(InputStream is) throws IOException {
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
        Map<String, List<String>> map = new HashMap<>();

        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    String provider = getStringValue(hssfRow.getCell(0)).trim();
                    String mediaId = getStringValue(hssfRow.getCell(1)).trim();
                    String name = getStringValue(hssfRow.getCell(2)).trim();
                    String roles = getStringValue(hssfRow.getCell(3)).trim();

                    if (StringUtils.isNotBlank(provider) && StringUtils.isNotBlank(mediaId)
                            && StringUtils.isNotBlank(roles)) {
                        List<String> values = new ArrayList<>();
                        String[] roleList = roles.split("，");
                        String json = JSON.toJSONString(roleList);
                        map.put(provider + "_" + mediaId + "_" + name, JSON.parseArray(json, String.class));
                    }

                }
            }
        }
        return map;
    }

    public static String getStringValue(XSSFCell hssfCell) {
        if (hssfCell != null && StringUtils.isNotBlank(hssfCell.getStringCellValue())) {
            return String.valueOf(hssfCell.getStringCellValue());
        }
        return "";
    }

    /**
     * 导出数据行为Excel格式
     *
     * @param wb        HSSFWorkbook对象
     * @param sheetName sheet名称
     * @param title     标题
     * @param rows      内容
     * @return
     */
    public static <E> XSSFWorkbook export2Workbook(XSSFWorkbook wb, String sheetName, LinkedHashMap<String, String> title, List<E> rows) {
        // 创建一个Workbook，对应一个Excel文件
        if (wb == null) {
            wb = new XSSFWorkbook();
        }
        // 创建单元格，并设置值表头 设置表头居中
        // 创建一个居中格式
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);

        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = null;
        if (StringUtils.isEmpty(sheetName)) {
            sheet = wb.createSheet();
        } else {
            sheet = wb.createSheet(sheetName);
        }

        // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = null;
        String[] headerArray = title.keySet().toArray(new String[0]);
        int headerSize = headerArray.length;
        for (int i = 0; i < headerSize; i++) {
            cell = row.createCell(i);
            cell.setCellValue(headerArray[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for (int n = 1; n <= rows.size(); n++) {
            row = sheet.createRow(n);

            E e = rows.get(n - 1);
            for (int i = 0; i < headerSize; i++) {
                String header = headerArray[i];
                String fieldName = title.get(header);
                // 通过反射机制，读取对象取值
                try {
                    Class<?> aClass = e.getClass();
                    Field field = aClass.getDeclaredField(fieldName);
                    if (field == null) {
                        continue;
                    }
                    field.setAccessible(true);
                    Object obj = field.get(e);
                    Class type = field.getType();
                    XSSFCell datacell = row.createCell(i);
                    if (type == Boolean.TYPE) {
                        datacell.setCellValue(String.valueOf(obj));
                    } else if (type == Byte.TYPE || type == Short.TYPE || type == Integer.TYPE || type == Long.TYPE) {
                        datacell.setCellValue(String.valueOf(obj));
                    } else if (type == Float.TYPE || type == Double.TYPE) {
                        datacell.setCellValue(String.valueOf(obj));
                    } else if (type == Character.TYPE) {
                        datacell.setCellValue(String.valueOf(obj));
                    } else if (type == Date.class) {
                        datacell.setCellValue(String.valueOf(obj));
                    } else {
                        datacell.setCellValue(String.valueOf(obj));
                    }
                } catch (NoSuchFieldException e1) {
                    LoggerFactory.getLogger(ExcelUtil.class).warn("row={},header={},field={},info=没有该字段", i + 1, header, fieldName);
                } catch (IllegalAccessException e1) {
                    LoggerFactory.getLogger(ExcelUtil.class).warn("row={},header={},field={},info=字段访问权限问题", i + 1, header, fieldName);
                }
            }
        }
        return wb;
    }

    /**
     * 保存文件到指定位置
     * @param fileName
     * @param wb
     * @throws Exception
     */
    public static void save(String fileName, XSSFWorkbook wb) throws Exception {
        String file = ConfigUtil.class.getClassLoader().getResource("application.properties").getFile();
        File parent = new File(new File(file).getParentFile().getParentFile(), "data");
        parent.mkdirs();
        FileOutputStream os = new FileOutputStream(new File(parent, fileName));
        wb.write(os);
        os.flush();
        os.close();
    }
}
